﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Reflection;
using Dapper;
using Newtonsoft.Json;
using API_Service.DBHelper;
using System.Configuration;

namespace API_Service.BizLogic.Bom
{
    public class BomService : BaseLogicService
    {
        public BomService() { }

        public ExecutionResult<dynamic> Check_BOM(string sn)
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = "select serial_number from r_wip_tracking_t where serial_number=@sn";

                    conn.Open();

                    int res = conn.Query(sql, new { sn = sn }).Count();

                    if (res > 0)
                    {
                        result.Status = true;
                        result.Message = "OK";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "SN(序列号: " + sn + ")输入错误或者不存在.";
                    }
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> GetBomDataByBomno(string bomNo)
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @" select key_part_no,part_code,bom_version,qty,unit,wastage_rate,alternative,sequence,operation,designator_location,
                                    remarks,is_valid from c_bom_keyparts_t where bom_no=@bomno ";

                    conn.Open();

                    result.Datas = conn.Query<dynamic>(sql, new { bomno = bomNo }).ToArray();
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> GetBomDataByBomPartNo(string bomNo,string partNo)
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = " select * from c_bom_keyparts_t where bom_no=@bomno and key_part_no=@partno ";

                    conn.Open();

                    result.Datas = conn.Query<dynamic>(sql, new { bomno = bomNo ,partno= partNo }).ToArray();
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> GetBomAlternaviveDataByPartNo(string viewModule)
        {
            var result = new ExecutionResult<dynamic>();

            dynamic vm = JsonConvert.DeserializeObject<dynamic>(viewModule);

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @" select  alternative_kpn,[replace_item],[unit],[qty] ,[sequence],[effective_date],[expiry_date],[remarks]
      ,is_valid from c_bom_alternative_t where bom_no=@bomno and key_part_no=@kpn ";

                    conn.Open();

                    result.Datas = conn.Query<dynamic>(sql, new { bomno = (string)vm.bomno, kpn=(string)vm.partno }).ToArray();
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> GetBomAlternaviveDataByAlternativePartNo(string bomno,string kpn,string akpn)
        {
            var result = new ExecutionResult<dynamic>();

            //dynamic vm = JsonConvert.DeserializeObject<dynamic>(viewModule);

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = " select * from c_bom_alternative_t where bom_no=@vbomno and key_part_no=@vkpn and alternative_kpn=@vakpn ";

                    conn.Open();

                    result.Datas = conn.Query<dynamic>(sql, new { vbomno = bomno, vkpn = kpn,vakpn=akpn }).ToArray();
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult AddBomPartNo(string vendorModel)
        {
            var result = new ExecutionResult();

            dynamic vm = JsonConvert.DeserializeObject<dynamic>(vendorModel);

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @" insert into c_bom_keyparts_t (bom_no,key_part_no,part_code,bom_version,qty,unit,wastage_rate,alternative,sequence,operation,
                    designator_location,remarks,is_valid,create_time,create_user,update_time,update_user)
                            values(@bom_no,@key_part_no,@part_code,@bom_version,@qty,@unit,@wastage_rate,@alternative,@sequence,@operaton,
                                @designator_location,@remarks,@is_valid,CONVERT(varchar(30),getdate(),20),
                                @create_user,CONVERT(varchar(30),getdate(),20),@update_user) ";

                    conn.Open();

                    int j = this.GetBomDataByBomno((string)vm.vBomNo).Datas.Length;
                    if (j == 0)
                    {
                        result.Status = false;
                        result.Message = (string)vm.vBomNo + " :此BOM不存在, 请确认.";
                    }
                    else
                    {
                        int i = this.GetBomDataByBomPartNo((string)vm.vBomNo, (string)vm.vPartNo).Datas.Length;

                        if (i > 0)
                        {
                            result.Status = false;
                            result.Message = (string)vm.vBomNo + " :Bom中 已经存在此料号的信息, 请确认.";
                        }
                        else
                        {
                            result.ExecuteCount = conn.Execute(sql, new
                            {
                                bom_no = (string)vm.vBomNo,           //"S001",
                                key_part_no = (string)vm.vPartNo,           //"大大科技",
                                bom_version = (string)vm.vBomVersion,           //"打算打打",
                                qty = (string)vm.vQty,           //"中国",
                                unit = (string)vm.vUnit,         //"18632147874",
                                wastage_rate = (string)vm.vWastageRate,     //"Simon.Zhang",
                                alternative = (string)vm.vAlternative,           //"打算打打",
                                sequence = (string)vm.vSequence,           //"中国",
                                operaton = (string)vm.vOperaton,         //"18632147874",
                                designator_location = (string)vm.vDesignatorLocation,     //"Simon.Zhang",
                                remarks = (string)vm.vRemarks,         //"18632147874",
                                is_valid = (string)vm.vIsValid,     //"Simon.Zhang",
                                create_user = (string)vm.cby,           //"打算打打",
                                update_user = (string)vm.mby,           //"中国",
                                part_code = (string)vm.vPartCode                  //cdate = DateTime.Now,
                                                                                  //mdate = DateTime.Now
                            });

                            if (result.ExecuteCount > 0)
                            {
                                result.Message = "BOM信息 " + (string)vm.vBomNo + ":" + (string)vm.vPartNo + " 添加成功.";
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult DeleteBomPartNo(string bomModel)
        {
            var result = new ExecutionResult();
            dynamic vm = JsonConvert.DeserializeObject<dynamic>(bomModel);

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql_bom = " delete c_bom_keyparts_t where bom_no=@abomno and key_part_no=@apartno ";

                    string sql_alternative = @" delete c_bom_alternative_t where bom_no=@abomno and key_part_no=@apartno  ";

                    conn.Open();

                    result.ExecuteCount = conn.Execute(sql_bom, new
                    {
                        abomno = (string)vm.bomno,
                        apartno = (string)vm.partno
                    });

                    if (result.ExecuteCount > 0)
                    {
                        result.Message = "Bom信息 " + (string)vm.bomno + "+" + (string)vm.partno + " 删除成功.";
                    }

                    result.ExecuteCount = conn.Execute(sql_alternative, new
                    {
                        abomno = (string)vm.bomno,
                        apartno = (string)vm.partno
                    });

                    if (result.ExecuteCount > 0)
                    {
                        result.Message = "Bom信息 " + (string)vm.bomno + "+" + (string)vm.partno+"+替代："+ (string)vm.alternativekpn + " 删除成功.";
                    }

                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult UpdateBomByPartNo(string vendorModel)
        {
            var result = new ExecutionResult();

            dynamic vm = JsonConvert.DeserializeObject<dynamic>(vendorModel);

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @"  update c_bom_keyparts_t 
                                    set    [bom_version]=@bom_version
                                          ,[qty]=@qty
                                          ,[unit]=@unit
                                          ,[wastage_rate]=@wastage_rate
                                          ,[alternative]=@alternative
                                          ,[sequence]=@sequence
                                          ,[operation]=@operation
                                          ,[designator_location]=@designator_location
                                          ,[remarks]=@remarks
                                          --,[erp_create_time]=@erp_create_time
                                          --,[erp_update_time]=@erp_update_time
                                          ,[is_valid]=@is_valid
                                          --,[create_time]=@create_time
                                          --,[create_user]=@create_user
                                          ,[update_time]=@update_time
                                          ,[update_user]=@update_user
                                        ,[part_code]=@part_code
                                    where bom_no=@bomno and key_part_no=@kpn    ";

                    conn.Open();

                    result.ExecuteCount = conn.Execute(sql, new
                    {
                        bomno = (string)vm.vBomNo,           //"S001",
                        kpn = (string)vm.vPartNo,           //"大大科技",
                        bom_version = (string)vm.vBomVersion,           //"打算打打",
                        qty = (string)vm.vQty,           //"中国",
                        unit = (string)vm.vUnit,         //"18632147874",
                        wastage_rate = (string)vm.vWastageRate,     //"Simon.Zhang",
                        alternative = (string)vm.vAlternative,           //"S001",
                        sequence = (string)vm.vSequence,           //"大大科技",
                        operation = (string)vm.vOperaton,           //"打算打打",
                        designator_location = (string)vm.vDesignatorLocation,           //"中国",
                        remarks = (string)vm.vRemarks,         //"18632147874",
                        //erp_create_time = (string)vm.vcontact,     //"Simon.Zhang",
                        //erp_update_time = (string)vm.vcode,           //"S001",
                        is_valid = (string)vm.vIsValid,           //"大大科技",
                        update_time = DateTime.Now,         //"18632147874",
                        update_user = (string)vm.mby,     //"Simon.Zhang",
                        part_code=(string)vm.vPartCode
                    });

                    if (result.ExecuteCount > 0)
                    {
                        result.Message = "Bom信息 " + (string)vm.vBomNo+":"+(string)vm.vPartNo + " 修改成功.";
                    }

                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }

                return result;
            }
        }
        public ExecutionResult AddBomAlternativePartNo(string vendorModel)
        {
            var result = new ExecutionResult();

            dynamic vm = JsonConvert.DeserializeObject<dynamic>(vendorModel);

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @" insert into c_bom_alternative_t(
                          bom_no ,key_part_no ,alternative_kpn,replace_item,unit,qty
                          ,sequence,effective_date,expiry_date,remarks,erp_create_time
                          ,erp_update_time,is_valid,create_time,create_user,update_time,update_user)
                    values(@bom_no ,@key_part_no ,@alternative_kpn,@replace_item,@unit,@qty
                          ,@sequence,@effective_date,@expiry_date,@remarks,@erp_create_date
                          ,@erp_update_date,@is_valid,@create_time,@create_user,@update_time,@update_user) ";

                    conn.Open();

                    int j = this.GetBomDataByBomPartNo((string)vm.vBomNo, (string)vm.vPartNo).Datas.Length;
                    if (j == 0)
                    {
                        result.Status = false;
                        result.Message = (string)vm.vBomNo + " :此BOM料号:"+ (string)vm.vPartNo + "不存在, 请确认.";
                    }
                    else
                    {
                        int i = this.GetBomAlternaviveDataByAlternativePartNo((string)vm.vBomNo, (string)vm.vPartNo,(string)vm.vAlternativePartNo).Datas.Length;

                        if (i > 0)
                        {
                            result.Status = false;
                            result.Message = (string)vm.vBomNo + " :替代Bom中 已经存在此料号的信息, 请确认.";
                        }
                        else
                        {
                           result.ExecuteCount = conn.Execute(sql, new
                            {
                                bom_no = (string)vm.vBomNo,
                                key_part_no = (string)vm.vPartNo,
                                alternative_kpn = (string)vm.vAlternativeKpn,
                                replace_item = (char)vm.vReplaceItem,
                                qty = Convert.ToInt32(vm.vQty),
                                unit = (string)vm.vUnit,
                                sequence = (string)vm.vSequence,
                                effective_date = Convert.ToDateTime(vm.vEffectiveDate),
                                expiry_date = Convert.ToDateTime(vm.vExpiryDate),
                                remarks = (string)vm.vRemarks,
                                erp_create_date = "",
                                erp_update_date = "",
                                is_valid = (char)vm.vIsValid,
                                create_user = (string)vm.cby,
                                update_user = (string)vm.mby,
                                create_time = DateTime.Now,
                                update_time = DateTime.Now
                            });

                            if (result.ExecuteCount > 0)
                            {
                                result.Message = "BOM信息 " + (string)vm.vBomNo + ":" + (string)vm.vPartNo + " 添加成功.";
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult UpdateBomAlternativeByPartNo(string vendorModel)
        {
            var result = new ExecutionResult();

            dynamic vm = JsonConvert.DeserializeObject<dynamic>(vendorModel);

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @" update c_bom_alternative_t
                        set replace_item=@replace_item,
                            unit=@unit,
	                        qty=@qty ,
	                        sequence=@sequence,
	                        effective_date=@effective_date,
	                        expiry_date=@expiry_date,
	                        remarks=@remarks,
	                        -- erp_create_date=@erp_create_date,
                            -- erp_update_date=@erp_update_date,
	                        is_valid=@is_valid,	
	                        update_time=@update_time,
	                        update_user=@update_user
                        where bom_no=@bomno and key_part_no=@kpn and alternative_kpn=@akpn   ";

                    conn.Open();

                    result.ExecuteCount = conn.Execute(sql, new
                    {
                        bomno = (string)vm.vBomNo,
                        kpn = (string)vm.vPartNo,
                        akpn = (string)vm.vAlternativeKpn,
                        replace_item = (string)vm.vReplaceItem,
                        qty = (string)vm.vQty,
                        unit = (string)vm.vUnit,
                        sequence = (string)vm.vSequence,
                        effective_date = (DateTime)vm.vEffectiveDate,
                        expiry_date = (DateTime)vm.vExpiryDate,
                        remarks = (string)vm.vRemarks,
                        //erp_create_date = "",
                        //erp_update_date = "",
                        is_valid = (string)vm.vIsValid,
                        //create_user = (string)vm.cby,
                        update_user = (string)vm.mby,
                        //create_time = DateTime.Now,
                        update_time = DateTime.Now
                    });

                    if (result.ExecuteCount > 0)
                    {
                        result.Message = "Bom信息 " + (string)vm.vBomNo + ":" + (string)vm.vPartNo + " 修改成功.";
                    }

                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }

                return result;
            }
        }
        public ExecutionResult DeleteBomAlternativePartNo(string bomModel)
        {
            var result = new ExecutionResult();

            dynamic vm = JsonConvert.DeserializeObject<dynamic>(bomModel);

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql_alternative = @" delete c_bom_alternative_t where bom_no=@bomno and key_part_no=@kpn and  alternative_kpn=@akpn";

                    conn.Open();

                    result.ExecuteCount = conn.Execute(sql_alternative, new
                    {
                        bomno = (string)vm.bomno,
                        kpn = (string)vm.kpn,
                        akpn = (string)vm.akpn
                    });

                    if (result.ExecuteCount > 0)
                    {
                        result.Message = "Bom信息 " + (string)vm.bomno + "+" + (string)vm.partno + "+替代：" + (string)vm.alternativekpn + " 删除成功.";
                    }

                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> GetPartModeList()
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @" select * from c_part_code_t where is_valid='Y' order by part_code  ";

                    conn.Open();

                    result.Datas = conn.Query<dynamic>(sql).ToArray();
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> GetBOMList()
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @" SELECT DISTINCT bom_no FROM dbo.c_bom_keyparts_t  ";

                    conn.Open();

                    result.Datas = conn.Query<dynamic>(sql).ToArray();
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }

    }
}